package com.web.util;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelUtil {

	
	public static List<List<String>> readXlsx(String path){
		List<List<String>> result=new ArrayList<List<String>>();
		try {
			InputStream input=new FileInputStream(path);
			XSSFWorkbook workbook=new XSSFWorkbook(input);
			
			for(Sheet xssfSheet:workbook){
				if(xssfSheet==null){
					continue;
				}
				for(int rowNum=1;rowNum<=xssfSheet.getLastRowNum();rowNum++){
					XSSFRow row=(XSSFRow) xssfSheet.getRow(rowNum);
					int minCellNum=row.getFirstCellNum();
					int maxCellNum=row.getLastCellNum();
					List<String>rowList=new ArrayList<String>();
					for(int i=minCellNum;i<maxCellNum;i++){
						XSSFCell cell=row.getCell(i);
						if(cell==null){
							continue;
						}
						rowList.add(cell.toString());
					}
					result.add(rowList);
				}
			}
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return result;
	}
	
	public static List<List<String>> readXls(String path){
		List<List<String>> result=new ArrayList<List<String>>();
		try {
			InputStream input=new FileInputStream(path);
			HSSFWorkbook workbook=new HSSFWorkbook(input);
			for(int numSheet=0;numSheet<workbook.getNumberOfSheets();numSheet++){
				HSSFSheet sheet=workbook.getSheetAt(numSheet);
				if(sheet==null){
					continue;
				}
				for(int rowNum=1;rowNum<=sheet.getLastRowNum();rowNum++){
					HSSFRow row=sheet.getRow(rowNum);
					int minCellNum=row.getFirstCellNum();
					int maxCellNum=row.getLastCellNum();
					List<String> rowList=new ArrayList<String>();
					for(int i=minCellNum;i<maxCellNum;i++){
						HSSFCell cell=row.getCell(i);
						if(cell==null){
							continue;
						}
						rowList.add(getStringVal(cell));
					}
							result.add(rowList);
				}
			}
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return result;
	}

	private static String getStringVal(HSSFCell cell) {
		switch (cell.getCellType()) {
		case Cell.CELL_TYPE_BOOLEAN:
			return cell.getBooleanCellValue() ? "TRUE" : "FALSE";
		case Cell.CELL_TYPE_FORMULA:
			return cell.getCellFormula();
		case Cell.CELL_TYPE_NUMERIC:
			cell.setCellType(Cell.CELL_TYPE_STRING);
			return cell.getStringCellValue();
		case Cell.CELL_TYPE_STRING:
			return cell.getStringCellValue();
		default:
			return null;
		}
	}

}
